/**
 * @Author: SevDaisy十七散人 
 * @Date: 2020-07-04 12:51:16
 */
package zyy.control;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Date;

import javax.swing.JOptionPane;

import zyy.model.BeanAdminUserinfo;
import zyy.model.BeanUsers;
import zyy.ui.FrmUserInfo;
import zyy.util.BaseException;
import zyy.util.Bus;
import zyy.util.BusinessException;
import zyy.util.DBUtil;
import zyy.util.DbException;

public class UsersManager {
  public BeanUsers login(String loginName, String uPwd) throws BaseException {
    /*
     * 参数合法性检测
     */
    if (loginName == null || "".equals(loginName) || loginName.length() > 255) {
      throw new BusinessException("登陆账号 必须是 1~255 个字符");
    }
    if (uPwd == null || "".equals(uPwd) || uPwd.length() > 255) {
      throw new BusinessException("密码 必须是 1~255 个字符");
    }

    BeanUsers result = null;
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT UserID,uPasswd,uName from Users where loginName=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setString(1, loginName);
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        if (rs.getString(2).equals(uPwd)) {
          result = new BeanUsers();
          result.setUserID(rs.getInt(1));
          result.setLoginName(loginName);
          result.setuPasswd(uPwd);
          result.setuName(rs.getString(3));
        } else {
          throw new BusinessException("账号密码不匹配！");
        }
      } else {
        throw new BusinessException("查无此账号");
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    // Link.currentUser = result;
    return result;
  }

  public void reg(String lName, String uName, String Passwd1, String Passwd2, String Tel, String Email, String City,
      int Sex) throws BusinessException {
    // BeanUsers result = null;
    if (lName == null || lName.equals("") || lName.length() > 255) {
      throw new BusinessException("登录账户合法性：\n1、非空\n2、不大于255字符");
    }
    if (uName == null || uName.equals("") || uName.length() > 255) {
      throw new BusinessException("用户昵称合法性：\n1、非空\n2、不大于255字符");
    }
    if (!Passwd1.equals(Passwd2)) {
      throw new BusinessException("两次密码输入应该一致");
    }
    if (Passwd1 == null || Passwd1.equals("") || Passwd1.length() > 255) {
      throw new BusinessException("密码合法性：\n1、非空\n2、不大于255字符");
    }

    Tel = (Tel == null ? "" : Tel);
    Email = (Email == null ? "" : Email);
    City = (City == null ? "" : City);

    // 查询 有没有重名账户名
    // select UserID from Users where loginName=?
    // 插入 insert into
    // Users(uName,uSex,uTelnumber,uEmail,uCity,uRegDate,loginName,uPasswd)
    // values(?,?,?,?,?,?,?,?)
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "select UserID from Users where loginName=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setString(1, lName);
      if (pst.executeQuery().next()) {
        throw new BusinessException("该账户名已被占用");
      }
      sql = "insert into Users(uName,uSex,uTelnumber,uEmail,uCity,uRegDate,loginName,uPasswd) values(?,?,?,?,?,?,?,?)";
      pst = conn.prepareStatement(sql);
      pst.setString(1, uName);
      pst.setInt(2, Sex);
      pst.setString(3, Tel);
      pst.setString(4, Email);
      pst.setString(5, City);
      pst.setTimestamp(6, new java.sql.Timestamp(System.currentTimeMillis()));
      pst.setString(7, lName);
      pst.setString(8, Passwd1);
      pst.executeUpdate();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    // return result;
  }

  public void del(BeanAdminUserinfo curVUser) throws BusinessException {
    // 查看当前用户是否有未完成的订单，若有，则抛出不可删除
    // 删除用户，然后删除相关的所有订单数据，和订单详情数据。
    // 删除用我自己写好的函数(new OrderManager()).removeByOrderID(int:OrderID)
    // 似乎。。。不需要事务控制
    // 最后，再删除用户本身
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "select count(v.OrderID) from viewadminuser v where v.oStatus not in ('已确认收货') and v.UserID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, curVUser.getUserID());
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        if (rs.getInt(1) > 0) {
          throw new BusinessException("该用户尚有未完成的订单，不可删除");
        }
      }
      sql = "SELECT OrderID from Orders WHERE UserID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, curVUser.getUserID());
      rs = pst.executeQuery();
      while (rs.next()) {
        int kid = rs.getInt(1);
        System.out.printf("预备删除订单 %s:%d\n", curVUser.getuName(), kid);
        Bus.orderManager.removeByOrderID(kid);
      }
      sql = "DELETE FROM Users WHERE UserID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, curVUser.getUserID());
      pst.executeUpdate();
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } catch (DbException e) {
      e.printStackTrace();
      throw new BusinessException("删除订单数据 失败");
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void setVIP(int UserID, String tarTime) throws BusinessException {
    try {
      Bus.sdf.parse(tarTime);
    } catch (ParseException ex) {
      JOptionPane.showMessageDialog(null, ex.getMessage() + "\nyyyy-MM-dd HH:mm:ss\n" + tarTime, "错误",
          JOptionPane.ERROR_MESSAGE);
      ex.printStackTrace();
    }
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "UPDATE Users SET uVIPtill=? WHERE UserID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setString(1, tarTime);
      pst.setInt(2, UserID);
      pst.executeUpdate();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void checkVIP(int UserID) throws BusinessException {
    Connection conn = null;
    Date now = new Date();
    Date set;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT u.uVIP, u.uVIPtill from Users u WHERE UserID = ?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, UserID);
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        set = Bus.sdf.parse(rs.getString(2));
        sql = "UPDATE Users SET uVIP=? WHERE UserID=?";
        pst = conn.prepareStatement(sql);
        pst.setInt(2, UserID);
        if (now.before(set)) {
          if (rs.getInt(1) == 0) {
            pst.setInt(1, 1);
            pst.executeUpdate();
          }
        } else {
          if (rs.getInt(1) == 1) {
            pst.setInt(1, 0);
            pst.executeUpdate();
          }
        }
      } else {
        throw new BusinessException("查无此账号");
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } catch (ParseException e) {
      e.printStackTrace();
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void loadUserInfo(FrmUserInfo f) throws BusinessException {
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT u.uName, u.loginName, u.uCity, u.uEmail, u.uTelnumber, u.uVIP, u.uVIPtill FROM Users u WHERE UserID = ?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentUser.getUserID());
      java.sql.ResultSet rs = pst.executeQuery();
      rs.next();
      f.uNameCon.setText(Bus.infoVal(rs.getString(1)));
      f.lNameCon.setText(Bus.infoVal(rs.getString(2)));
      f.uCityCon.setText(Bus.infoVal(rs.getString(3)));
      f.uEmailon.setText(Bus.infoVal(rs.getString(4)));
      f.uTeleCon.setText(Bus.infoVal(rs.getString(5)));
      if (rs.getInt(6) == 0) {
        f.isVIPCon.setText("否");
      } else {
        f.isVIPCon.setText("是");
      }
      /*
       * Date vDate = rs.getDate(7); String sDate; if (vDate == null) { sDate =
       * this.infoVal(null); } else { sDate = Bus.sdf.format(vDate); }
       */
      f.vipTimeCon.setText(Bus.infoVal(rs.getString(7)));
      sql = "SELECT count(OrderID), SUM(oRealValue) FROM Orders WHERE UserID = ?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentUser.getUserID());
      rs = pst.executeQuery();
      rs.next();
      f.oCntCon.setText(Bus.infoVal(rs.getString(1)));
      f.oSumCon.setText(Bus.infoVal(rs.getString(2)));
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void changePasswd(int UserID, String oriPwd, String nPwd, String nPwds) throws BusinessException {
    if (nPwd == null || nPwd.equals("") || nPwd.length() > 255)
      throw new BusinessException("密码应为不大于255字符的非空字符串");
    if (!nPwd.equals(nPwds))
      throw new BusinessException("两次密码输入应当一致");
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT uPasswd from Users where UserID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, UserID);
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        if (rs.getString(1).equals(oriPwd)) {
          sql = "UPDATE Users SET uPasswd=? WHERE UserID=?";
          pst = conn.prepareStatement(sql);
          pst.setString(1, nPwd);
          pst.setInt(2, UserID);
          pst.executeUpdate();
        } else {
          throw new BusinessException("账号密码不匹配！");
        }
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public static void main(String[] args) {
    BeanAdminUserinfo p = new BeanAdminUserinfo();
    p.setUserID(7);
    p.setuName("元气小可爱");
    try {
      Bus.userManager.del(p);
    } catch (BusinessException e) {
      e.printStackTrace();
    }
  }
}